package com.yc.qunxing.mapper;

import com.yc.qunxing.entity.Orders;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yc.qunxing.entity.vo.RouteFrequency;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author 鹏哥
 * @since 2024-05-23
 */
public interface OrdersMapper extends BaseMapper<Orders> {

    @Insert("INSERT INTO orders(user_id,status,departure_location,destination_location,duration,distance)" +
            "values(#{userId}, #{status}, #{departureLocation}, #{destinationLocation}, #{duration}, #{distance})")
    @Options(useGeneratedKeys = true, keyProperty = "orderId", keyColumn = "order_id")
    void insertAndGetId(Orders orders);

    @Select("SELECT departure_location, destination_location, COUNT(*) as frequency " +
            "FROM orders " +
            "WHERE user_id = #{userId} " +
            "GROUP BY departure_location, destination_location " +
            "ORDER BY frequency DESC")
    List<RouteFrequency> getRouteFrequenciesByUserId(@Param("userId") Integer userId);

    @Select("SELECT order_id, status, driver_id FROM orders WHERE user_id = #{userId}")
    List<Orders> getOrdersByUserId(Integer userId);

    @Select("SELECT order_date, total_amount , actual_amount , departure_location, destination_location FROM orders WHERE user_id = #{userId}")
    List<Orders> getOrderDetailsByUserId(Integer userId);

    @Select("SELECT order_id, status, user_id FROM orders WHERE driver_id = #{driverId}")
    List<Orders> getOrdersByDriverId(Integer driverId);

    @Select("SELECT order_date, total_amount, departure_location, destination_location FROM orders WHERE driver_id = #{driverId}")
    List<Orders> getOrderDetailsByDriverId(Integer driverId);

    @Select("SELECT COUNT(*) FROM orders WHERE user_id = #{userId} AND status = '5'")
    int countOrdersWithStatus5(Integer userId);

    @Select("SELECT DATE_FORMAT(order_date, '%Y-%m') as month, COUNT(*) as count " +
            "FROM orders WHERE status != 4 GROUP BY month")
    List<Map<String, Object>> getOrderCountPerMonth();

    @Select("SELECT DATE_FORMAT(o.order_date, '%Y-%m') as month, dv.vehicle_type, COUNT(*) as count " +
            "FROM orders o " +
            "LEFT JOIN driver_vehicles dv ON o.driver_id = dv.driver_id " +
            "WHERE o.status != 4 " +
            "GROUP BY month, dv.vehicle_type")
    List<Map<String, Object>> getVehicleTypeCountPerMonth();

    @Select("SELECT DATE_FORMAT(o.order_date, '%Y-%m') as month, dv.color, COUNT(*) as count " +
            "FROM orders o " +
            "LEFT JOIN driver_vehicles dv ON o.driver_id = dv.driver_id " +
            "WHERE o.status != 4 " +
            "GROUP BY month, dv.color")
    List<Map<String, Object>> getColorCountPerMonth();
}
